package org.afun.csms_android.utils;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import org.afun.csms_android.domain.Course;
import org.afun.csms_android.domain.Stu2Score;
import org.afun.csms_android.domain.Student;
import org.afun.csms_android.domain.Teacher;
import org.afun.csms_android.domain.Teacher2Course;
import org.afun.csms_android.domain.User;
import org.afun.csms_android.domain.javaBean.SS;
import org.afun.csms_android.domain.javaBean.SS2;
import org.afun.csms_android.domain.javaBean.TS;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * Created by aFun on 20222/5/20.
 */

// 数据库操作类
public class TableOperator {
    private SQLiteDatabase sqLiteDatabase;

    public TableOperator() {

    }

    public TableOperator(SQLiteDatabase sqLiteDatabase) {
        this.sqLiteDatabase = sqLiteDatabase;
    }

    // 检查表中是否存在该账号
    public boolean check(String account, String password) {
        Cursor cursor = null;
        String sql = "select password from user where account = ?";
        try {
            cursor = sqLiteDatabase.rawQuery(sql, new String[]{String.valueOf(account)});
            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    @SuppressLint("Range") String passwd = cursor.getString(cursor.getColumnIndex("password"));
                    if (passwd.equals(password)) {
                        return true;
                    }
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return false;
    }

    // 修改密码
    public boolean alterPwd(User user, String newPasswd) {
        // 检查账号是否存在
        String sqlFind = "select * from user where account=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sqlFind, new String[]{user.getAccount()});
        if (cursor.getCount() > 0) {
            // 修改密码
            String sql = "update user set password = ? where account = ?";
            sqLiteDatabase.execSQL(sql, new Object[]{newPasswd, user.getAccount()});
            return true;
        }
        cursor.close();
        return false;
    }


    // ======================================管理员操作===================================================
    // 查询所有用户信息
    public List<User> queryAllUser() {
        String sql = "select * from user";
        Cursor result = sqLiteDatabase.query("user", new String[]{"id", "account", "password", "gender", "roleId", "name"}, null, null, null, null, null);
        return this.ConvertToUser(result);
    }

    // cursor to user
    @SuppressLint("Range")
    private List<User> ConvertToUser(Cursor cursor) {
        int count = cursor.getCount();
        if (count == 0 || !cursor.moveToFirst()) {
            return null;
        }
        List<User> users = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            User user = new User();
            user.setId(cursor.getInt(cursor.getColumnIndex("id")));
            user.setAccount(cursor.getString(cursor.getColumnIndex("account")));
            user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            user.setGender(cursor.getString(cursor.getColumnIndex("gender")));
            user.setRoleId(cursor.getInt(cursor.getColumnIndex("roleId")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            users.add(user);
            cursor.moveToNext();
        }
        return users;
    }

    // 添加用户
    public boolean addUser(User user) {
        long flag1 = 1;
        long flag2 = 1;
        try {
            // random id
            int myRandomId = (int) (Math.random() * 10000 + 1);
            ContentValues values = new ContentValues();
            values.put("id", myRandomId);
            values.put("password", user.getPassword());
            values.put("account", user.getAccount());
            values.put("gender", user.getGender());
            values.put("roleId", user.getRoleId());
            values.put("name", user.getName());
            flag1 = sqLiteDatabase.insertOrThrow("user", null, values);
            switch (user.getRoleId()) {
                case 1:
                    break;
                case 2:
                    // teacher
                    ContentValues contentValues = new ContentValues();
                    contentValues.put("teaId", myRandomId);
                    contentValues.put("account", user.getAccount());
                    flag2 = sqLiteDatabase.insertOrThrow("teacher", null, contentValues);
                    break;
                case 3:
                    // student
                    ContentValues contentValues2 = new ContentValues();
                    contentValues2.put("stuId", myRandomId);
                    contentValues2.put("account", user.getAccount());
                    flag2 = sqLiteDatabase.insertOrThrow("student", null, contentValues2);
                    break;
                default:
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            return flag1 > 0 && flag2 > 0;
        }
    }

    // 修改用户信息
    public boolean updateUserById(User user) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("password", user.getPassword());
        contentValues.put("gender", user.getGender());
        contentValues.put("name", user.getName());
        int flag = sqLiteDatabase.update("user", contentValues, "id=" + user.getId(), null);
        return flag > 0;
    }

    // 删除用户信息
    public boolean deleteUserById(int userId) {
        int flag = sqLiteDatabase.delete("user", "id=" + userId, null);
        return flag > 0;
    }

    // 显示待审核的课程
    public List<Course> showAuditCourse() {
        String sql = "select couId,couName,credit,isAudit from course";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
        return this.ConvertToCourse(cursor);
    }

    // cursor to course
    @SuppressLint("Range")
    private List<Course> ConvertToCourse(Cursor cursor) {
        int count = cursor.getCount();
        if (count == 0 || !cursor.moveToFirst()) {
            return null;
        }
        List<Course> courses = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            Course course = new Course();
            course.setCouId(cursor.getInt(cursor.getColumnIndex("couId")));
            course.setCouName(cursor.getString(cursor.getColumnIndex("couName")));
            course.setCredit(cursor.getInt(cursor.getColumnIndex("credit")));
            course.setIsAudit(cursor.getInt(cursor.getColumnIndex("isAudit")));
            courses.add(course);
            cursor.moveToNext();
        }
        return courses;
    }

    // 课程审核
    public boolean auditCourse(Course course) {
        // 审核状态: 0=>审核未通过 1=>审核中 2=>审核通过
        ContentValues contentValues = new ContentValues();
        contentValues.put("isAudit", course.getIsAudit());
        int flag = sqLiteDatabase.update("course", contentValues, "couId=" + course.getCouId(), null);
        return flag > 0;
    }

    // 依据id查询用户
    @SuppressLint("Range")
    public User findByUserId(int userId) {
        String sql = "select * from user where id=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{String.valueOf(userId)});
        if (cursor.getCount() > 0) {
            cursor.moveToNext();
            User user = new User();
            user.setId(userId);
            user.setAccount(cursor.getString(cursor.getColumnIndex("account")));
            user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            user.setGender(cursor.getString(cursor.getColumnIndex("gender")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setRoleId(cursor.getInt(cursor.getColumnIndex("roleId")));
            return user;
        } else {
            return null;
        }
    }

    // 依据account查询用户
    @SuppressLint("Range")
    public User findByUserAccount(String account) {
        String sql = "select * from user where account=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{account});
        if (cursor.getCount() > 0) {
            cursor.moveToNext();
            User user = new User();
            user.setId(cursor.getInt(cursor.getColumnIndex("id")));
            user.setAccount(cursor.getString(cursor.getColumnIndex("account")));
            user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            user.setGender(cursor.getString(cursor.getColumnIndex("gender")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setRoleId(cursor.getInt(cursor.getColumnIndex("roleId")));
            return user;
        } else {
            return null;
        }
    }


    // ======================================管理员操作===================================================


    // ======================================教师操作===================================================
    // 展示本人所拥有的学生
    // javaBean => stuId stuName gender couId couName score
    @SuppressLint("Range")
    public List<TS> showMyStudent(Teacher teacher) {
        // 先在tea2cou表中寻找teaId=teacher.getId的学生  => ssId,stuId
        ContentValues contentValues = new ContentValues();
        contentValues.put("teaId", teacher.getTeaId());
        String sql = "select ssId,stuId,couId,score from stu2score where teaId=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{String.valueOf(teacher.getTeaId())});
        List<TS> ts = this.convertToTS(cursor);
        for (int i = 0; i < Objects.requireNonNull(ts).size(); i += 1) {
            TS t = ts.get(i);
            // 根据couId寻找Course表 => 获取couName
            int couId = t.getCouId();
            String sql1 = "select couName from course where couId=?";
            Cursor cursor1 = sqLiteDatabase.rawQuery(sql1, new String[]{String.valueOf(couId)});
            cursor1.moveToNext();
            String couName = cursor1.getString(cursor1.getColumnIndex("couName"));
            ts.get(i).setCouName(couName);

            // 根据此stuId寻找student表 => 获取account
            int stuId = t.getStuId();
            String sql2 = "select account from student where stuId=?";
            cursor1 = sqLiteDatabase.rawQuery(sql2, new String[]{String.valueOf(stuId)});
            cursor1.moveToNext();
            String account = cursor1.getString(cursor1.getColumnIndex("account"));

            // 根据account => 获取name,gender
            String sql3 = "select name,gender from user where account=?";
            cursor1 = sqLiteDatabase.rawQuery(sql3, new String[]{account});
            cursor1.moveToNext();
            String name = cursor1.getString(cursor1.getColumnIndex("name"));
            String gender = cursor1.getString(cursor1.getColumnIndex("gender"));

            ts.get(i).setStuName(name);
            ts.get(i).setGender(gender);
            cursor1.close();
        }
        return ts;
    }

    @SuppressLint("Range")
    private List<TS> convertToTS(Cursor cursor) {
        int count = cursor.getCount();
        if (count == 0 || !cursor.moveToFirst()) {
            return null;
        }
        List<TS> tsRet = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            TS ts = new TS();
            ts.setSsId(cursor.getInt(cursor.getColumnIndex("ssId")));
            ts.setStuId(cursor.getInt(cursor.getColumnIndex("stuId")));
            ts.setCouId(cursor.getInt(cursor.getColumnIndex("couId")));
            ts.setScore(cursor.getInt(cursor.getColumnIndex("score")));
            cursor.moveToNext();
            tsRet.add(ts);
        }
        return tsRet;
    }

    // 记录成绩
    public boolean recordScore(Stu2Score stu2Score) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("score", stu2Score.getScore());
        int flag = sqLiteDatabase.update("stu2score", contentValues, "ssId=" + stu2Score.getSsid(), null);
        return flag > 0;
    }

    // 查询tea2cou中 teaId=??? 的记录
    private List<Teacher2Course> queryT2CRecord(Teacher teacher) {
        int teaId = teacher.getTeaId();
        String sql2 = "select * from tea2cou where teaId=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql2, new String[]{String.valueOf(teaId)});
        return this.ConvertToT2C(cursor);
    }

    // 查询教师已有的课程
    public List<Course> getMyCourse(Teacher teacher) {
        List<Teacher2Course> teacher2Courses = queryT2CRecord(teacher);
        StringBuilder sb = new StringBuilder("select * from course where couId in (");
        for (int i = 0; i < teacher2Courses.size(); i += 1) {
            sb.append(teacher2Courses.get(i).getCouId());
            // 不是最后一个则需要加逗号
            if (i != teacher2Courses.size() - 1) {
                sb.append(",");
            }
        }
        sb.append(")");
        String sql = sb.toString();
        // 筛选课程信息：教师有这门课，则显示
        Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
        return this.ConvertToCourse(cursor);
    }

    // 查询课程供教师选课
    public List<Course> findCourseForTeaSelect(Teacher teacher) {
        List<Teacher2Course> teacher2Courses = queryT2CRecord(teacher);

        StringBuilder sb = new StringBuilder("select * from course where isAudit=2 and couId not in (");
        for (int i = 0; i < teacher2Courses.size(); i += 1) {
            sb.append(teacher2Courses.get(i).getCouId());
            // 不是最后一个则需要加逗号
            if (i != teacher2Courses.size() - 1) {
                sb.append(",");
            }
        }
        sb.append(")");
        String sql = sb.toString();
        // 筛选课程信息：当教师已经选了这门课，则移除
        Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
        return this.ConvertToCourse(cursor);
    }

    // cursor to tea2cou
    @SuppressLint("Range")
    private List<Teacher2Course> ConvertToT2C(Cursor cursor) {
        int count = cursor.getCount();
        if (count == 0 || !cursor.moveToFirst()) {
            return null;
        }
        List<Teacher2Course> t2cs = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            Teacher2Course t2c = new Teacher2Course();
            t2c.setTcId(cursor.getInt(cursor.getColumnIndex("tcId")));
            t2c.setCouId(cursor.getInt(cursor.getColumnIndex("couId")));
            t2c.setTeaId(cursor.getInt(cursor.getColumnIndex("teaId")));
            t2cs.add(t2c);
            cursor.moveToNext();
        }
        return t2cs;

    }

    // 选择课程
    public boolean teacherSelectCourse(Course course, Teacher teacher) {
        ContentValues contentValues = new ContentValues();
        int myRandomId = (int) (Math.random() * 10000 + 1);
        contentValues.put("tcId", myRandomId);
        contentValues.put("teaId", teacher.getTeaId());
        contentValues.put("couId", course.getCouId());
        long flag = sqLiteDatabase.insertOrThrow("tea2cou", null, contentValues);
        return flag > 0;
    }

    // 添加课程
    public boolean addCourse(Course course) {
        ContentValues contentValues = new ContentValues();
        int myRandomId = (int) (Math.random() * 10000 + 1);
        contentValues.put("couId", myRandomId);
        contentValues.put("couName", course.getCouName());
        contentValues.put("credit", course.getCredit());
        contentValues.put("isAudit", 1);         // 添加需要审核
        long flag = sqLiteDatabase.insertOrThrow("course", null, contentValues);
        return flag > 0;
    }

    // 通过account查询教师
    @SuppressLint("Range")
    public Teacher findTeacherByAccount(String account) {
        String sql = "select * from teacher where account=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{account});
        if (cursor.getCount() > 0) {
            cursor.moveToNext();
            Teacher teacher = new Teacher();
            teacher.setTeaId(cursor.getInt(cursor.getColumnIndex("teaId")));
            teacher.setAccount(account);
            return teacher;
        } else {
            return null;
        }
    }

    // ======================================教师操作===================================================


    // ======================================学生操作===================================================
    // 显示本人成绩
    public List<SS> showMyScore(Student student) {
        String sql = "select ssId,stuId,couId,score,teaId from stu2score where stuId=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{String.valueOf(student.getStuId())});
        List<SS> sses = this.ConvertSS(cursor);
        for (SS ss : sses) {
            int couId = ss.getCouId();
            Course course = this.findByCouId(couId);
            ss.setCouName(course.getCouName());
            ss.setCredit(course.getCredit());
            int teaId = ss.getTeaId();
            ss.setTeaName(this.findTeaNameByTeaId(teaId));
        }

        return sses;
    }

    // cursor to StudentScore
    @SuppressLint("Range")
    private List<SS> ConvertSS(Cursor cursor) {
        int count = cursor.getCount();
        if (count == 0 || !cursor.moveToFirst()) {
            return null;
        }
        List<SS> ret = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            SS ss = new SS();
            ss.setSsId(cursor.getInt(cursor.getColumnIndex("ssId")));
            ss.setStuId(cursor.getInt(cursor.getColumnIndex("stuId")));
            ss.setCouId(cursor.getInt(cursor.getColumnIndex("couId")));
            ss.setScore(cursor.getInt(cursor.getColumnIndex("score")));
            ss.setTeaId(cursor.getInt(cursor.getColumnIndex("teaId")));
            ret.add(ss);
            cursor.moveToNext();
        }
        return ret;
    }

    // 通过couId寻找couName
    @SuppressLint("Range")
    public Course findByCouId(int couId) {
        String sql = "select couName,credit from course where couId=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{String.valueOf(couId)});
        if (cursor.getCount() == 0 || !cursor.moveToFirst()) {
            return null;
        } else {
            String couName = cursor.getString(cursor.getColumnIndex("couName"));
            int credit = cursor.getInt(cursor.getColumnIndex("credit"));
            Course course = new Course();
            course.setCouName(couName);
            course.setCredit(credit);
            return course;
        }
    }

    // 通过teaId寻找到teaName
    @SuppressLint("Range")
    public String findTeaNameByTeaId(int teaId) {
        String sql = "select name from user where account=(select account from teacher where teaId=?)";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{String.valueOf(teaId)});
        if (cursor.getCount() == 0 || !cursor.moveToFirst()) {
            return null;
        } else {
            return cursor.getString(cursor.getColumnIndex("name"));
        }
    }

    // 显示学生现拥有的课程
    public List<SS> showStudentCourse(Student student) {
        List<SS> sses = this.showMyScore(student);
        return sses;
    }

    @SuppressLint("Range")
    // 显示学生可选课程
    public List<SS2> showCourse2Select(Student student) {
        List<SS2> ret = new ArrayList<>();

        String sql = "select couId from stu2score where stuId=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{String.valueOf(student.getStuId())});
        List<Course> courses = new ArrayList<>();
        while (cursor.moveToNext()) {
            Course course = new Course();
            course.setCouId(cursor.getInt(cursor.getColumnIndex("couId")));
            courses.add(course);
        }
        StringBuilder record = new StringBuilder("select * from course where couId not in (");
        for (int i = 0; i < courses.size(); i += 1) {
            record.append(courses.get(i).getCouId());
            if (i != courses.size() - 1) {
                record.append(",");
            }
        }
        record.append(")");
        String sql2 = record.toString();
        // 可选课程
        // 循环利用游标
        cursor = sqLiteDatabase.rawQuery(sql2, null);
        List<Course> canSelect = ConvertToCourse(cursor);

        // 寻找可选课程有哪些教师
        // couId couName teaId teaName +credit
        for (int i = 0; i < canSelect.size(); i++) {
            int couId = canSelect.get(i).getCouId();
            String couName = canSelect.get(i).getCouName();
            String sql3 = "select teaId from tea2cou where couId=?";
            cursor = sqLiteDatabase.rawQuery(sql3, new String[]{String.valueOf(couId)});
            SS2 ss = new SS2();
            ss.setCouId(couId);
            ss.setCouName(couName);
            List<Integer> teaIds = new ArrayList<>();
            List<String> teaNames = new ArrayList<>();
            while (cursor.moveToNext()) {
                int teaId = cursor.getInt(cursor.getColumnIndex("teaId"));
                String teaName = this.findTeaNameByTeaId(teaId);
                teaIds.add(teaId);
                teaNames.add(teaName);
            }
            ss.setTeaId(teaIds);
            ss.setTeaName(teaNames);
            ret.add(ss);
        }

        return ret;
    }

    // 学生选课
    public boolean selectCourse(Stu2Score stu2Score) {
        // random id
        int myRandomId = (int) (Math.random() * 10000 + 1);
        ContentValues contentValues = new ContentValues();
        contentValues.put("ssId", myRandomId);
        contentValues.put("stuId", stu2Score.getStuId());
        contentValues.put("couId", stu2Score.getCouId());
        contentValues.put("score", stu2Score.getScore());
        contentValues.put("teaId", stu2Score.getTeaId());
        System.out.println(myRandomId);
        long flag = sqLiteDatabase.insertOrThrow("stu2score", null, contentValues);
        return flag > 0;
    }

    // 通过account查询学生
    @SuppressLint("Range")
    public Student findStudentByAccount(String account) {
        String sql = "select * from student where account=?";
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{account});
        if (cursor.getCount() > 0) {
            cursor.moveToNext();
            Student student = new Student();
            student.setStuId(cursor.getInt(cursor.getColumnIndex("stuId")));
            student.setAccount(account);
            return student;
        } else {
            return null;
        }
    }

    // ======================================学生操作===================================================

}
